package com.ctrip.ec.dal;

/**
 * Created by jiang on 2018/3/22.
 */

import org.apache.tomcat.jdbc.pool.DataSource;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;


public class DbAccess {
    private final static ConcurrentHashMap<String, DataSource> dsMap = new ConcurrentHashMap<String, DataSource>();

    private final static ThreadLocal<Connection> CONNECTION_THREAD_LOCAL = new ThreadLocal<Connection>();
    private final static ThreadLocal<Integer> TRANS_COUNT_THREAD_LOCAL = new ThreadLocal<Integer>();

    private DataSourceManager dsm;

    private int getTransCount() {
        Integer transCount = TRANS_COUNT_THREAD_LOCAL.get();
        if (transCount == null) {
            return 0;
        } else {
            return transCount;
        }
    }

    private void setTransCount(int transCount) {

        if (transCount == 0) {
            TRANS_COUNT_THREAD_LOCAL.remove();
        } else {
            TRANS_COUNT_THREAD_LOCAL.set(transCount);
        }
    }

    public DbAccess(String configFileName) {

        dsm = new DataSourceManager(configFileName);
    }

    public DbAccess() {
        dsm = new DataSourceManager();
    }

    public DbProvider getProvider() {

        if (dsm.getDataSource().getDriverClassName().equals("com.mysql.jdbc.Driver")) {
            return DbProvider.MYSQL;
        } else {
            return DbProvider.MSSQL;
        }

    }


    public int execute(String sql, Object... paras) throws SQLException {

        PreparedStatement pst = null;
        Connection conn = null;
        try {
            conn = this.getConnection();
            // pst = conn.prepareStatement(sql);
            pst = prepareStatement(conn, sql, paras);
            //  setParas(pst, paras);
            return pst.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw ex;
        } finally {
            if (pst != null) {
                pst.close();
            }
            if (this.getTransCount() == 0 && conn != null) {
                conn.close();
            }

        }
    }


    private PreparedStatement prepareStatement(Connection conn, String sql, Object... paras) throws SQLException {
        Map.Entry<String, List<Integer>> newSqls = filterSql(sql);
        PreparedStatement pst = conn.prepareStatement(newSqls.getKey());
        List<Integer> index = newSqls.getValue();
        if (sql.contains("?")) {//sql里全是?
            int i = 1;
            for (Object obj : paras) {
                pst.setObject(i, obj);
                i++;
            }
        } else {

            int maxLength = index.size() == 0 ? 0 : index.stream().mapToInt(e -> e).max().getAsInt();
            if (maxLength > paras.length) {
                throw new RuntimeException("参数个数不匹配");
            }
            for (int i = 0; i < index.size(); i++) {
                pst.setObject(i + 1, paras[index.get(i)]);
            }

        }

        return pst;

    }
//    private void setParas(PreparedStatement pst, Object... paras) throws SQLException {
//        int i = 1;
//        for (Object obj : paras) {
//            pst.setObject(i, obj);
//            i++;
//        }
//    }

    public  List<Map<String,Object>> query(String sql , Object ... paras) throws SQLException{
        PreparedStatement pst = null;
        Connection conn = null;
        try {
            conn = getConnection();

            pst = prepareStatement(conn, sql, paras);

            ArrayList<Map<String,Object>> list = new ArrayList<>();
            ResultSet rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String,Object> info = convertToMap(rs,rsmd,colCount);
                //  Info info = convert(classType, rs, rsmd, colCount);
                list.add(info);
            }
            rs.close();
            return list;

        } catch (SQLException ex) {
            ex.printStackTrace();
            throw ex;
        } finally {
            if (pst != null) {
                pst.close();
            }
            if (this.getTransCount() == 0 && conn != null) {
                try {
                    conn.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                    throw ex;
                }
            }

        }
    }

    private Map<String, Object> convertToMap(ResultSet rs, ResultSetMetaData rsmd, int colCount) throws SQLException {
        Map<String,Object> map = new HashMap<>();
        for (int i = 0; i < colCount; i++) {
            String colName = rsmd.getColumnName(i + 1);
            Object val = rs.getObject(colName);

            map.put(colName,val);
            //((EntityBase) info).fieldValue(colName, val);
        }

        return map;
    }


    public <Info> List<Info> query(Class<Info> classType, String sql, Object... paras) throws SQLException {
        PreparedStatement pst = null;
        Connection conn = null;
        try {
            conn = getConnection();

            pst = prepareStatement(conn, sql, paras);

            ArrayList<Info> list = new ArrayList<Info>();
            ResultSet rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int colCount = rsmd.getColumnCount();
            while (rs.next()) {
                Info info = convert(classType, rs, rsmd, colCount);
                list.add(info);
            }
            rs.close();
            return list;

        } catch (SQLException ex) {
            ex.printStackTrace();
            throw ex;
        } finally {
            if (pst != null) {
                pst.close();
            }
            if (this.getTransCount() == 0 && conn != null) {
                try {
                    conn.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                    throw ex;
                }
            }

        }
    }

    private <Info> Info convert(Class<Info> clazz, ResultSet rs, ResultSetMetaData rsmd, int colCount) throws SQLException {
        Info info;
        if (clazz == Integer.class || clazz == Long.class || clazz == Float.class
                || clazz == Double.class || clazz == Boolean.class || clazz == Short.class || clazz == String.class || clazz == BigDecimal.class) {

            info = (Info) rs.getObject(1);
        } else {
            try {
                info = clazz.newInstance();
            } catch (Exception ex) {
                ex.printStackTrace();
                throw new RuntimeException("new instance error");
            }
            for (int i = 0; i < colCount; i++) {
                String colName = rsmd.getColumnName(i + 1);
                Object val = rs.getObject(colName);
                ((EntityBase) info).fieldValue(colName, val);
            }
        }
        return info;
    }


    public <T> T getScalar(Class<T> clazz, String sql, Object... paras) throws SQLException {
        List<T> list = query(clazz, sql, paras);
        if(list.size() == 0){
            return null;
        }
        Object t = list.get(0);
        Object r ;
        if(t.getClass() != clazz){
            if(clazz == Long.class){
                r = new Long((Integer)t);
            }
            else{
                r = new Integer(((Long)t).intValue());
            }
        }else{
            r = t;
        }

        return (T)r ;
    }

    public Connection getConnection() throws SQLException {
        Connection conn = null;
        if (this.getTransCount() == 0) {
            conn = dsm.getDataSource().getConnection();
        } else {
            conn = CONNECTION_THREAD_LOCAL.get();

        }
       // conn.getSchema()
        return conn;
    }

    public void begin() throws SQLException {
        int transCount = this.getTransCount();
        if (transCount == 0) {
            Connection conn = dsm.getDataSource().getConnection();
            conn.setAutoCommit(false);
            CONNECTION_THREAD_LOCAL.set(conn);
        }
        this.setTransCount(this.getTransCount() + 1);
    }

    public void rollback() throws SQLException {

        try {
            if (this.getTransCount() > 0) {
                this.setTransCount(0);
                Connection conn = CONNECTION_THREAD_LOCAL.get();
                conn.rollback();
                conn.setAutoCommit(true);
                conn.close();
                CONNECTION_THREAD_LOCAL.remove();
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            //this.close();
        }

    }

    public void commit() throws SQLException {
        try {
            this.setTransCount(this.getTransCount() - 1);
            if (this.getTransCount() == 0) {
                Connection conn = CONNECTION_THREAD_LOCAL.get();
                conn.commit();
                conn.setAutoCommit(true);
                conn.close();

                CONNECTION_THREAD_LOCAL.remove();
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {

        }
    }

    private Map.Entry<String, List<Integer>> filterSql(String sql) {

        // ArrayList<String> paras = new ArrayList();
        ArrayList<Integer> parasIndexs = new ArrayList<>();

        StringBuilder newSql = new StringBuilder();

        StringBuilder singlePara = new StringBuilder();
        boolean isPara = false;
        for (Character ch : sql.toCharArray()) {
            if (ch == '@' || ch == '$') {
                isPara = true;
                newSql.append("?");
            }
            if (isPara && (ch == ' ' || ch == ',' || ch == '=' || ch == ')' )) {
                isPara = false;
            }
            if (isPara) {
                singlePara.append(ch);
            } else {
                newSql.append(ch);
                if (singlePara.length() > 0) {
                    // paras.add(sb.toString());
                    parasIndexs.add(Integer.parseInt(singlePara.toString().substring(1)));

                    singlePara.setLength(0);
                }
            }
        }
        if (isPara) {
            parasIndexs.add(Integer.parseInt(singlePara.toString().substring(1)));
        }

        HashMap<String, List<Integer>> resutl = new HashMap<>();
        resutl.put(newSql.toString(), parasIndexs);
        return resutl.entrySet().iterator().next();

    }


    public String genEntity(String tableName, String className) throws SQLException {
        StringBuilder sb = new StringBuilder();
        if (className == null || className.length() == 0) {

            className = tableName;
            if (className.indexOf("_") > -1) {
                className = className.substring(className.indexOf('_'));
            }
            className = getPascalName(className) + "Entity";
        }
        sb.append("@DbTable(\"" + tableName + "\")\r\n");
        sb.append("public class " + className + " extends EntityBase {\r\n");
        String sql = "select * from " + tableName + " where 1=2";
        Connection conn = dsm.getDataSource().getConnection();
        PreparedStatement pst = conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int colCount = rsmd.getColumnCount();
        StringBuilder getset = new StringBuilder();
        String pkName = null;//表第一列为主键
        String[] fields = new String[colCount - 1];//第一个主键不要
        String[] setMethods = new String[colCount];
        String[] getMethods = new String[colCount];
        String[] columns = new String[colCount];//所有列名，包括pk;
        String[] colClazz = new String[colCount];//所有列的类型

        for (int i = 0; i < colCount; i++) {
            String[] cls = rsmd.getColumnClassName(i + 1).split("\\.");
            String colClassName = cls[cls.length - 1];
            colClazz[i] = colClassName;
            String colName = rsmd.getColumnName(i + 1);
            if (i == 0) {//第一个主键
                pkName = colName;
            } else {
                fields[i - 1] = colName;
            }
            columns[i] = colName;
            colName = getPascalName(colName);
            setMethods[i] = "set" + colName;
            getMethods[i] = "get" + colName;
            String lowColName = colName.substring(0, 1).toLowerCase() + colName.substring(1);

            if (colClassName.equals("Timestamp")) {
                sb.append("@JsonFormat(pattern=\"yyyy-MM-dd HH:mm:ss\",locale = \"zh\" ,timezone=\"GMT+8\")\r\n");
            }

            if (colClassName.equals("Date")) {
                sb.append("@JsonFormat(pattern=\"yyyy-MM-dd\",locale = \"zh\" ,timezone=\"GMT+8\")\r\n");
            }
            sb.append("@DbColumn(\"" + columns[i] + "\")\r\n");
            sb.append("private " + colClassName + " " + lowColName + "; \r\n");

            getset.append("public  " + colClassName + " get" + colName + "(){\r\n");
            getset.append("return this." + lowColName + ";\r\n");
            getset.append("}\r\n");

            getset.append("public void set" + colName + "(" + colClassName + " " + lowColName + "){\r\n");
            getset.append("this." + lowColName + " = " + lowColName + ";\r\n");
            getset.append("}\r\n");

        }
        sb.append(getset);
        sb.append("}");
        return sb.toString();
    }

    public String genDao(String tableName) throws SQLException{
        StringBuilder sb = new StringBuilder();
        String className  = tableName;
        if (className.indexOf("_") > -1) {
            className = className.substring(className.indexOf('_'));
        }
        String  entityClassName = getPascalName(className) + "Entity";
        className = getPascalName(className) + "Dao";

        sb.append("@Service\r\n");
        sb.append("public class "+ className + " extends DaoBase<" + entityClassName+ "> {\r\n");
        sb.append(" public " + className + "() {\r\n");
        sb.append("super(" + entityClassName + ".class);\r\n");
        sb.append("}\r\n");
        sb.append("public " + className + "(DbAccess db) {\r\n");
        sb.append("super("+ entityClassName + ".class,db);\r\n");
        sb.append("}\r\n");
        sb.append("}\r\n");

        return sb.toString();

    }

    public  String genController(String tableName) throws SQLException{

        String prefix  = tableName;
        if (prefix.indexOf("_") > -1) {
            prefix = prefix.substring(prefix.indexOf('_')+1);
        }
        String  entityClassName = getPascalName(prefix) + "Entity";
        String daoClassName = getPascalName(prefix) + "Dao";
        String daoName = getPascalName(prefix).substring(0,1).toLowerCase() + getPascalName(prefix).substring(1);
        String className= getPascalName(prefix) + "Controller";
        String mappingUrl = getPascalName(prefix);
        mappingUrl= mappingUrl.substring(0,1).toLowerCase() + mappingUrl.substring(1);
//        for(String str : prefix.split("_")){
//            mappingUrl+="/"+str;
//        }
        StringBuilder sb = new StringBuilder();
        sb.append("@RestController\r\n");
        sb.append("@RequestMapping(\"" +mappingUrl+ "\")\r\n");
        sb.append(" public class " + className +  "{\r\n");
        sb.append("@Autowired\r\n");
        sb.append("private "+ daoClassName + " "  + daoName + ";\r\n");
//        sb.append("@GetMapping(\"list\")\r\n");
//        sb.append("public List<" + entityClassName + "> list() throws SQLException {\r\n");
//        sb.append("List<" + entityClassName +"> list = " +daoName + ".list();\r\n");
//        sb.append("return list;\r\n");
//        sb.append("}\r\n");
        sb.append("\r\n");
        sb.append("@PostMapping(\"list\")\r\n");
        sb.append("public List<"+ entityClassName + "> list(@RequestBody QueryModel model) throws  SQLException{\r\n");
        sb.append("PagerSetting setting = model.build();\r\n");
        sb.append("List<" + entityClassName + "> list = " + daoName + ".list(setting);\r\n");
        sb.append("RequestContext.setResponseHeaderCount(setting.getRecordCount());\r\n");
        sb.append("return list;\r\n");
        sb.append("}\r\n");
        sb.append("\r\n");
        sb.append("@GetMapping(\"detail/{id}\")\r\n");
        sb.append("public  " +entityClassName+ " detail(@PathVariable Long id) throws SQLException {\r\n");
        sb.append("return "  + daoName + ".entity(id);\r\n");
        sb.append("}\r\n");
        sb.append("\r\n");
        sb.append("@PostMapping(\"save\")\r\n");
        sb.append("public  Long save(@RequestBody "+ entityClassName +" entity) throws SQLException {\r\n");
        sb.append( daoName + ".save(entity);\r\n");
        sb.append("return entity.getId();\r\n");
        sb.append("}\r\n");
        sb.append("\r\n");
        sb.append("@PostMapping(\"delete\")\r\n");
        sb.append("public  void delete(@RequestBody List<Long> ids) throws SQLException {\r\n");
        sb.append(daoName + ".delete(ids);\r\n");
        sb.append("}\r\n");
        sb.append("@PostMapping(\"erase\")\r\n");
        sb.append(" public void erase(@RequestBody List<Long> ids) throws  SQLException{\r\n");
        sb.append(daoName + ".erase(ids);\r\n");
        sb.append("}\r\n");
        sb.append("\r\n");
        sb.append("@PostMapping(\"unerase\")\r\n");
        sb.append("public  void unerase(@RequestBody List<Long> ids) throws  SQLException{\r\n");
        sb.append(daoName + ".unerase(ids);\r\n");
        sb.append("}\r\n");
        sb.append("}\r\n");


        return sb.toString();

    }


    public String getPascalName(String src) {

        String target = "";
        //  var count = 0;//下划线的个数
        Boolean upper = false;

        for (Character c : src.toCharArray()) {
            if (target == "") {
                upper = true;
            }

            if (c == '_') {
                upper = true;
            } else {
                if (upper) {
                    target += c.toString().toUpperCase();
                    upper = false;
                } else {
                    target += c;
                }

            }
        }

        return target;
    }


}
